﻿-- IMMUNIZATION COVERAGE
CREATE VIEW COVERAGE_REPORT_INDICATOR AS
WITH POPULATION AS (
	SELECT "CODE", "DOB_YEAR", "DOB_MONTH", COUNT(*) AS POPULATION
		FROM (
			SELECT *, EXTRACT(YEAR FROM "BIRTHDATE") AS "DOB_YEAR", EXTRACT(MONTH FROM "BIRTHDATE") AS "DOB_MONTH" FROM "CHILD" LEFT JOIN "HEALTH_FACILITY" ON ("CHILD"."HEALTHCENTER_ID" = "HEALTH_FACILITY"."ID") 
		) AS "CHILD"
		GROUP BY "CODE", "DOB_MONTH", "DOB_YEAR"
),
VACCINATIONS AS (
	SELECT "HEALTH_FACILITY"."CODE" AS "HF_CODE", "CVX_CODE", "DOSE"."DOSE_NUMBER", EXTRACT(YEAR FROM "VACCINATION_DATE") AS "VACC_YEAR", EXTRACT(MONTH FROM "VACCINATION_DATE") AS "VACC_MONTH", "DOSE"."FULLNAME", "VACCINATION_STATUS", "NONVACCINATION_REASON_ID", CASE WHEN "VACCINATION_DATE" - "BIRTHDATE" < 365 THEN 'UNDER 1 YEAR' ELSE 'OVER 1 YEAR' END AS "AGE_GROUP", "COHORT" FROM
		"VACCINATION_EVENT" INNER JOIN "DOSE" ON ("VACCINATION_EVENT"."DOSE_ID" = "DOSE"."ID")
		INNER JOIN "SCHEDULED_VACCINATION" ON ("DOSE"."SCHEDULED_VACCINATION_ID" = "SCHEDULED_VACCINATION"."ID") 
		INNER JOIN "ITEM" ON ("ITEM"."ID" = "SCHEDULED_VACCINATION"."ITEM_ID")
		INNER JOIN "HL7_VACCINES" ON ("HL7_VACCINES"."ID" = "ITEM"."HL7_VACCINE_ID")
		INNER JOIN "HEALTH_FACILITY" ON ("VACCINATION_EVENT"."HEALTH_FACILITY_ID" = "HEALTH_FACILITY"."ID")
		INNER JOIN "CHILD" ON ("VACCINATION_EVENT"."CHILD_ID" = "CHILD"."ID")
		INNER JOIN "HEALTH_FACILITY_COHORT_DATA" ON ("HEALTH_FACILITY"."ID" = "HEALTH_FACILITY_COHORT_DATA"."HEALTH_FACILITY_ID" AND EXTRACT(YEAR FROM "VACCINATION_DATE") = "HEALTH_FACILITY_COHORT_DATA"."YEAR")
		LEFT JOIN "PLACE" ON ("HEALTH_FACILITY"."ID" = "PLACE"."HEALTH_FACILITY_ID")
		WHERE "VACCINATION_EVENT"."IS_ACTIVE" = TRUE
),
COVERAGE AS (
	SELECT VACCINATIONS."FULLNAME", "CVX_CODE", "DOSE_NUMBER", VACCINATIONS."HF_CODE", "VACC_MONTH", "VACC_YEAR",
		COUNT(*) AS TARGET,
		MAX("COHORT") AS COHORT, 
		SUM(CASE WHEN "VACCINATION_STATUS" = TRUE THEN 1 ELSE 0 END) AS GIVEN,
		SUM(CASE WHEN "VACCINATION_STATUS" = FALSE AND "NONVACCINATION_REASON_ID" <> 0 THEN 1 ELSE 0 END) AS SKIPPED,
		"AGE_GROUP"
		FROM VACCINATIONS 
		GROUP BY VACCINATIONS."FULLNAME", "CVX_CODE", "DOSE_NUMBER", "AGE_GROUP", VACCINATIONS."HF_CODE", "VACC_YEAR", "VACC_MONTH"
) 
SELECT *, (CAST(GIVEN AS REAL)/TARGET) * 100 AS COVERAGE, CASE WHEN COHORT > 0 THEN (CAST(GIVEN AS REAL)/COHORT) * 100 ELSE NULL END AS ADMIN_COVERAGE FROM COVERAGE;

-- VIEW FOR THE WEIGHT SUMMARY
CREATE VIEW CHILD_WEIGHT_SUMMARY AS 
SELECT "MONTH", "YEAR", "HEALTH_FACILITY_CODE", "AGE_ID", SUM(UNDERWEIGHT) AS UNDERWEIGHT, SUM(NORMAL) AS NORMAL, SUM(OVERWEIGHT) AS OVERWEIGHT 
FROM (
	-- DISCRETE MEASURES (FROM FORMS AND MOBILE APP)
	WITH CHILD_WEIGHT AS
	(
		SELECT "WEIGHT", "DATE", "CHILD_WEIGHT"."MODIFIED_BY", CASE WHEN "GENDER" THEN 'M' ELSE 'F' END AS "GENDER", ("CHILD_WEIGHT"."DATE" - "CHILD"."BIRTHDATE")::INT AS "AGE_AT_MEASURE" FROM
			"CHILD_WEIGHT" INNER JOIN "CHILD" ON ("CHILD_ID" = "CHILD"."ID") 
	)
	SELECT "MONTH", "YEAR", "HEALTH_FACILITY_CODE", "GENDER",
		SUM(CASE WHEN "ZSCORE" <= -2 THEN 1 ELSE 0 END) AS underweight, 
		SUM(CASE WHEN "ZSCORE" BETWEEN -2 AND 3 THEN 1 ELSE 0 END) AS normal,
		SUM(CASE WHEN "ZSCORE" >= 3 THEN 1 ELSE 0 END) AS OVERWEIGHT,
		"AGE_ID"
	FROM
	(
		SELECT EXTRACT(MONTH FROM "DATE") AS "MONTH",
			EXTRACT(YEAR FROM "DATE") AS "YEAR",
			CASE WHEN "WEIGHT" < "ZNEG4" THEN -4
				WHEN "WEIGHT" BETWEEN "ZNEG4" AND "ZNEG3" THEN -3
				WHEN "WEIGHT" BETWEEN "ZNEG3" AND "ZNEG2" THEN -2
				WHEN "WEIGHT" BETWEEN "ZNEG2" AND "ZNEG1" THEN -1
				WHEN "WEIGHT" BETWEEN "ZNEG1" AND "Z1" THEN 0
				WHEN "WEIGHT" BETWEEN "Z1" AND "Z2" THEN 1
				WHEN "WEIGHT" BETWEEN "Z2" AND "Z3" THEN 2
				WHEN "WEIGHT" BETWEEN "Z3" AND "Z4" THEN 3
				WHEN "WEIGHT" > "Z4" THEN 4
			END
			AS "ZSCORE",
			"HEALTH_FACILITY"."CODE" AS "HEALTH_FACILITY_CODE",
			"REF_CHILD_WEIGHT"."GENDER",
			"WEIGHT_AGE_RANGES".ID AS "AGE_ID"
			FROM CHILD_WEIGHT 
			INNER JOIN "REF_CHILD_WEIGHT" ON ("AGE_AT_MEASURE" = "REF_CHILD_WEIGHT"."AGE_DAY" AND CHILD_WEIGHT."GENDER" = "REF_CHILD_WEIGHT"."GENDER")
			INNER JOIN "USER" ON (CHILD_WEIGHT."MODIFIED_BY" = "USER"."ID")
			INNER JOIN "HEALTH_FACILITY" ON ("HEALTH_FACILITY"."ID" = "USER"."HEALTH_FACILITY_ID")
			INNER JOIN "WEIGHT_AGE_RANGES" ON ("AGE_AT_MEASURE" BETWEEN "FROM_DAYS" AND "TO_DAYS")
	) AS A GROUP BY "MONTH", "YEAR", "HEALTH_FACILITY_CODE", "GENDER", "AGE_ID"
	UNION
	-- AGGREGATE MEASURES ALREADY IN DB
	SELECT EXTRACT(MONTH FROM ENTRY_DATE) AS "MONTH", EXTRACT(YEAR FROM ENTRY_DATE) AS "YEAR", "HEALTH_FACILITY"."CODE", GENDER AS "GENDER", UNDERWEIGHT, NORMALWEIGHT, OVERWEIGHT, age_id
		FROM 
		"WEIGHT_TALLY" INNER JOIN "HEALTH_FACILITY" ON ("WEIGHT_TALLY".HEALTHFACILITY_ID = "HEALTH_FACILITY"."ID")
) AS A GROUP BY "MONTH", "YEAR","HEALTH_FACILITY_CODE", "AGE_ID";
		